USE [arirang_klist]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fs_Load_Acc_Invoice]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fs_Load_Acc_Invoice]
GO

/****** Object:  StoredProcedure [dbo].[[fs_Load_Acc_Invoice]]    Script Date: 04/04/2013 16:21:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[fs_Load_Acc_Invoice]
 @cIDVoucher CHAR(13), 
 @cUnit VARCHAR(33), 
 @cMTable VARCHAR(33), 
 @cDTable VARCHAR(33) 
AS
BEGIN
 DECLARE @strSQL NVARCHAR(4000)
 IF @cIDVoucher = ''
  BEGIN
   SET @strSQL = 'SELECT TOP 12'
   SET @strSQL = @strSQL + ' a.*,b.name as customer_name'
   SET @strSQL = @strSQL + ' INTO #phtmp '
   SET @strSQL = @strSQL + ' FROM ' + @cMTable + ' a left join tbl_customer b on a.customer_id = b.id'
   SET @strSQL = @strSQL + ' ORDER BY a.invoice_date DESC, a.stt_rec DESC'
  END
 ELSE
  BEGIN
   SET @strSQL = 'SELECT TOP 12'
   SET @strSQL = @strSQL + ' a.*'
   SET @strSQL = @strSQL + ' INTO #phtmp '
   SET @strSQL = @strSQL + ' FROM ' + @cMTable + ' a ' 
   SET @strSQL = @strSQL + ' WHERE a.stt_rec = ' + CHAR(39) + @cIDVoucher + CHAR(39)   
  END
 SET @strSQL = @strSQL+ CHAR(13) + 'SELECT a.*, b.name ' 
 SET @strSQL = @strSQL + ' INTO #cttmp FROM ' + @cDTable  + ' a left join tbl_product b on a.product_id = b.id'
 SET @strSQL = @strSQL + ' WHERE a.stt_rec IN (SELECT stt_rec FROM #phtmp)'
 SET @strSQL = @strSQL + CHAR(13) + ' SELECT * FROM #phtmp ORDER BY stt_rec'
 SET @strSQL = @strSQL + CHAR(13) + ' SELECT * FROM #cttmp ORDER BY stt_rec, stt_rec0'
 EXEC sp_executesql @strSQL
END

GO


